﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TalentPools.Model;
using System.Data;
using System.Data.SqlClient;
namespace TalentPools.DAL
{
    public class Company_LoginService
    {
        #region 读取数据表中数据的基本方法
        public List<Company_Login> GetCompanyLogin(CommandType commandType, string commandText, params SqlParameter[] paras)
        {
            List<Company_Login> list = new List<Company_Login>();
            using (SqlDataReader read = DBHelper.ExecuteGetReader(commandType, commandText, paras))
            {
                while (read.Read())
                {
                    Company_Login company = new Company_Login();
                    company.Id = Convert.ToInt32(read["Id"]);
                    company.CompanyName = read["CompanyName"].ToString();
                    company.CompanyPwd = read["CompanyPwd"].ToString();
                    company.RegTime = Convert.ToDateTime(read["RegTime"]);
                    company.Question = read["Question"].ToString();
                    company.Answer = read["Answer"].ToString();
                    company.IpAdd = read["IpAdd"].ToString();
                    list.Add(company);
                }
                return list;
            }
        }
        #endregion

        #region 插入企业登录信息
        public bool InsertCompany_Login(Company_Login comLogin)
        {
            string sql = "Insert into Company_Login(CompanyName,CompanyPwd,RegTime,Question,Answer,IsPast,Email,IpAdd,IsVip,Istj) values(@CompanyName,@CompanyPwd,@RegTime,@Question,@Answer,@isPast,@Email,@IpAdd,@IsVip,@Istj)";
            SqlParameter[] paras = new SqlParameter[]{
           new SqlParameter("@CompanyName",comLogin.CompanyName),
           new SqlParameter("@CompanyPwd",comLogin.CompanyPwd),
           new SqlParameter("@RegTime",comLogin.RegTime),
           new SqlParameter("@Question",comLogin.Question),
           new SqlParameter("@Answer",comLogin.Answer),
           new SqlParameter("@isPast",false),
           new SqlParameter("@IpAdd",comLogin.IpAdd),
           new SqlParameter("@Email",comLogin.Email),
           new SqlParameter("@IsVip",false),
           new SqlParameter("@Istj",false)
           };
            return DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras) == 1;
        }

        #endregion

        #region 根据用户名查找数据
        public object GetUserCount(string loginName)
        {
            string sql = "select count(*) from Company_Login where CompanyName=@loginName";
            SqlParameter para = new SqlParameter("@loginName", loginName);
            object o = DBHelper.ExecuteScalar(CommandType.Text, sql, para);
            return o;
        }
        public object GetUserCount(string loginName, string pwd)
        {
            string sql = "select COUNT(*) FROM Company_Login where CompanyName=@loginName and companyPwd=@pwd";
            SqlParameter[] paras = new SqlParameter[]{
           new SqlParameter("@loginName", loginName),
           new SqlParameter("@pwd",pwd)
           };
            object o = DBHelper.ExecuteScalar(CommandType.Text, sql, paras);
            return o;
        }
        public string GetUserState(string username)
        {
            string sql = "select isPast from Company_Login where CompanyName=@loginName";
            SqlParameter para = new SqlParameter("@loginName", username);
            return DBHelper.ExecuteScalar(CommandType.Text, sql, para).ToString();
        }
        #endregion

        #region 分页查询
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataSet GetLoginPage(int pageIndex, int pageSize, string where)
        {
            string sql = "select * from(select ROW_NUMBER() over(order by RegTime Desc)RowIndex,* from Company_Login where CompanyName like '%'+@where+'%')a where RowIndex between @begin and @end";
            SqlParameter[] paras = new SqlParameter[] {
            new SqlParameter("@begin",pageSize*(pageIndex-1)+1),
            new SqlParameter("@end",pageIndex*pageSize),
            new SqlParameter("@where",where)
            };
            using (DataSet ds = DBHelper.ExecuteDataSet(CommandType.Text, sql, paras))
            {
                return ds;
            }
        }
        /// <summary>
        /// 查询记录数
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public int GetLoginCount(string where)
        {
            string sql = "select count(*) from Company_Login where CompanyName like '%'+@where+'%'";
            SqlParameter para = new SqlParameter("@where", where);
            return (int)DBHelper.ExecuteScalar(CommandType.Text, sql, para);
        }
        #endregion

        #region 删除操作
        public bool DeleteLogin(int id)
        {
            string sql = "delete from Company_Login where Id=@id";
            SqlParameter para = new SqlParameter("@id", id);
            return DBHelper.ExecuteNonQuery(CommandType.Text, sql, para) > 0;
        }
        #endregion

        #region 更新注册信息状态
        /// <summary>
        /// 更改VIP状态
        /// </summary>
        /// <param name="id"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public bool UpdateState(int id, bool state)
        {
            string sql = "update Company_Login set IsVip=@state where Id=@id";
            SqlParameter[] paras = new SqlParameter[] { 
           new SqlParameter("@state",state),
           new SqlParameter("@id",id)
           };
            return DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras) > 0;
        }
        /// <summary>
        /// 更改推荐状态
        /// </summary>
        /// <param name="id"></param>
        /// <param name="t"></param>
        /// <returns></returns>
        public bool UpdateTj(int id, bool t)
        {
            string sql = "update Company_Login set Istj=@istj where Id=@id";
            SqlParameter[] paras = new SqlParameter[] { 
            new SqlParameter("@istj",t),
            new SqlParameter("@id",id)
            };
            return DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras) > 0;
        }
        /// <summary>
        /// 审核企业
        /// </summary>
        /// <param name="id"></param>
        /// <param name="b"></param>
        /// <returns></returns>
        public bool UpdatePast(int id, bool b)
        {
            string sql = "update Company_Login set IsPast=@isPast where Id=@id";
            SqlParameter[] paras = new SqlParameter[] { 
            new SqlParameter("@isPast",b),
            new SqlParameter("@id",id)
            };
            return DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras) > 0;
        }
        #endregion

        #region 修改密码
        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public bool EditPwd(string username, string password)
        {
            string sql = "update Company_Login set CompanyPwd='"+password+"' where CompanyName='"+username+"'";
            return DBHelper.ExecuteNonQuery(CommandType.Text, sql, null) > 0;
        }

        #endregion

        #region 查询密码
        public string CheckPwd(string companyname)
        {
            string sql = "select CompanyPwd from Company_Login where CompanyName=@CompanyName";
            SqlParameter para = new SqlParameter("@CompanyName", companyname);
            return DBHelper.ExecuteScalar(CommandType.Text, sql, para).ToString();
        }
        #endregion

        #region 查看企业是否是付费用户
        /// <summary>
        /// 查看企业是否是付费用户
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public string CheckVip(string username)
        {
            string sql = "select IsVip from company_Login where CompanyName='"+username+"'";
            return DBHelper.ExecuteScalar(CommandType.Text, sql, null).ToString();
        }
        #endregion
    }
}
